package com.scalar.chiptrack.operations.reports;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.HashMap;
import java.util.Map;
import java.util.LinkedHashMap;
import java.util.Stack;
import java.util.Iterator;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import java.io.IOException;

import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.scalar.ScalarException;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.ExcelUtils;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.commons.ExcelColumnProperties;

/**
 * Created by Veeru.
 * Date: Oct 18, 2005
 * Time: 7:04:17 PM
 */

public class DataDumpReportsHelper
{
    /**
     *
     * @param lotInfoList
     * @param reportHeading
     * @return
     * @throws ScalarException
     *
     * @deprecated use {@link DataDumpReportsHelper#getWorkbook(java.util.ArrayList, String, String, String,java.util.List<com.scalar.chiptrack.commons.ExcelColumnProperties>)}
     */
    public static Workbook getWorkbook ( ArrayList lotInfoList,  String reportHeading ) throws ScalarException
    {
        int lotInfoListSize   = ( lotInfoList == null ) ? 0 : lotInfoList.size();
        Workbook workbook;
        Sheet   sheet;
        if ("xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = ch.createDataFormat();

        sheet       = workbook.createSheet( reportHeading != null && reportHeading.length() > 31 ? reportHeading.substring(0, 29 )+".." : reportHeading );
        sheet.setSelected( true );

        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints ( ( short ) 12 );
        titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        titleFont.setFontName( "arial" );
        titleFont.setColor ( IndexedColors.BLACK.getIndex() );

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints ( ( short ) 8 );
        headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        headerFont.setFontName( "arial" );
        headerFont.setColor ( IndexedColors.BLACK.getIndex() );

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 8 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        textFont.setFontName( "arial" );
        textFont.setColor ( IndexedColors.BLACK.getIndex() );

        CellStyle titleStyle  = workbook.createCellStyle ( );
        titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
        titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
        titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        titleStyle.setWrapText ( true );
        titleStyle.setFont ( titleFont );

        CellStyle headerStyle  = workbook.createCellStyle ( );
        headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        headerStyle.setWrapText ( true );
        headerStyle.setFont ( headerFont );
        headerStyle.setDataFormat( df.getFormat( "@" ) );

        CellStyle textStyle  = workbook.createCellStyle ( );
        textStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        textStyle.setBorderTop ( CellStyle.BORDER_THIN );
        textStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        textStyle.setBorderRight ( CellStyle.BORDER_THIN );
        textStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        textStyle.setWrapText ( true );
        textStyle.setFont ( textFont );
        textStyle.setDataFormat( df.getFormat( "@" ) );
        
        CellStyle valueStyle  = workbook.createCellStyle ( );
        valueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        valueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        valueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        valueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        valueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        valueStyle.setWrapText ( true );
        valueStyle.setFont ( textFont );
        valueStyle.setDataFormat( df.getFormat( "###0;[Red](###0)" ) );   //BuiltinFormat "(#,##0_);[Red](#,##0)" )

        CellStyle dateValueStyle  = workbook.createCellStyle ( );
        dateValueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        dateValueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        dateValueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        dateValueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        dateValueStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        dateValueStyle.setWrapText ( true );
        dateValueStyle.setFont ( textFont );
        dateValueStyle.setDataFormat( df.getFormat("m/d/yy") );


        CellStyle valueFloatStyle  = workbook.createCellStyle ( );
        valueFloatStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        valueFloatStyle.setBorderTop ( CellStyle.BORDER_THIN );
        valueFloatStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        valueFloatStyle.setBorderRight ( CellStyle.BORDER_THIN );
        valueFloatStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        valueFloatStyle.setWrapText ( true );
        valueFloatStyle.setFont ( textFont );
        valueFloatStyle.setDataFormat( workbook.createDataFormat().getFormat("#,##0.000") );

        CellStyle numericvalueStyle  = workbook.createCellStyle ( );
        numericvalueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        numericvalueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        numericvalueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        numericvalueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        numericvalueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        numericvalueStyle.setWrapText ( true );
        numericvalueStyle.setFont ( textFont );
        numericvalueStyle.setDataFormat( workbook.createDataFormat().getFormat( "$ #,##0.0000;[Red]$ (#,##0.0000)" ) );

        Row row;

        ArrayList columnNames = (ArrayList)lotInfoList.get ( 0 );

        Cell cell = mergeCells ( sheet, 0, 0, 0, (columnNames.size()-1), titleStyle );
        setCellValue ( cell,  ch.createRichTextString(reportHeading), titleStyle );

        //SETTING HEADINGS
        row = sheet.createRow ( 2 );
        for ( int i = 0; i < columnNames.size(); i++ )
        {
            cell    = row.createCell( i );
            setCellValue ( cell, ch.createRichTextString((String)columnNames.get(i)), headerStyle );
            int width = ( ((String)columnNames.get(i)).length() > 8 ) ? ((String)columnNames.get(i)).length() : 8;
            sheet.setColumnWidth( i,  ( ( ( width + 4 ) * 256 ) ) );
        }

        int nextRowNumber   = 3;
        int nextColNumber;
        
        for ( int i = 1; i < lotInfoListSize; i++ )
        {
            nextColNumber   = 0;
            ArrayList colList =  ( ArrayList )lotInfoList.get ( i );
            row = sheet.createRow( nextRowNumber++ );
            for (int j = 0; j < colList.size(); j++) {
                DBColumn dbColumn = (DBColumn) colList.get(j);
                Object dbColumnObj = dbColumn.getColValObj();
                try
                {
                    if ( dbColumn.getColType() == java.sql.Types.INTEGER )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new Integer( 0 );
                        }

                        setCellValue( row, nextColNumber++, ((Integer)dbColumnObj).intValue(), valueStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.BIGINT )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new Long( 0 );
                        }
                        setCellValue( row, nextColNumber++, ((Long)dbColumnObj).longValue(), valueStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.FLOAT )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new Double( 0.0f );
                        }
                        setCellValue( row, nextColNumber++, ((Double)dbColumnObj).doubleValue(), valueFloatStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.DOUBLE )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new Double( 0.0f );
                        }
                        setCellValue( row, nextColNumber++, ((Double)dbColumnObj).doubleValue(), valueFloatStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.NUMERIC )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new BigDecimal( 0.0f );
                        }
                        setCellValue( row, nextColNumber++, ((BigDecimal)dbColumnObj).doubleValue(), numericvalueStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.VARCHAR )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  "";
                        }
                        setCellValue( row, nextColNumber++, ch.createRichTextString(dbColumnObj.toString()), textStyle );
                        if ( ( ( dbColumnObj.toString().length() + 4 ) * 256 ) > sheet.getColumnWidth( j ) )
                        {
                            sheet.setColumnWidth( j, (short) ( ( ( dbColumnObj.toString().length() + 4 ) * 256 ) ) );
                        }
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.TIMESTAMP )
                    {
                        //setCellValue( row, nextColNumber++, StringUtils.formatDateToString ( (Date)dbColumnObj , null ), textStyle );
                    	if( dbColumnObj == null )
                    	{
                    		setCellValue( row, nextColNumber++, ch.createRichTextString(""), textStyle );
                    	}
                    	else
                    	{
                    		setCellValue( row, nextColNumber++, ch.createRichTextString(StringUtils.formatDateToString ( (Date)dbColumnObj , null ) ), dateValueStyle );
                    	}
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.DATE )
                    {
                        //setCellValue( row, nextColNumber++, StringUtils.formatDateToString ( (Date)dbColumnObj , null ), textStyle );
                    	if( dbColumnObj == null )
                    	{
                    		setCellValue( row, nextColNumber++, ch.createRichTextString(""), textStyle );
                    	}
                    	else
                    	{
                    		setCellValue( row, nextColNumber++, ch.createRichTextString(StringUtils.formatDateToString ( (Date)dbColumnObj , null ) ), dateValueStyle );
                    	}
                    }
                    else
                    {
                        setCellValue( row, nextColNumber++, ch.createRichTextString(""), textStyle );
                    }

                }
                catch( Exception e)
                {
                    setCellValue( row, nextColNumber++, ch.createRichTextString(dbColumnObj.toString()), textStyle );
                    e.printStackTrace();
                }
            }
        }

        return workbook;
    }

    public static Workbook getWorkbook ( ArrayList lotInfoList, String reportHeading, String numberFormat ) throws ScalarException
    {
        int lotInfoListSize   = ( lotInfoList == null ) ? 0 : lotInfoList.size();
        Workbook workbook;
        Sheet   sheet;
        if ("xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook    = new XSSFWorkbook();
        }
        else
        {
            workbook    = new HSSFWorkbook();
        }
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df = ch.createDataFormat();

        sheet       = workbook.createSheet( reportHeading );
        sheet.setSelected( true );

        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints ( ( short ) 12 );
        titleFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        titleFont.setFontName( "arial" );
        titleFont.setColor ( IndexedColors.BLACK.getIndex() );

        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints ( ( short ) 8 );
        headerFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        headerFont.setFontName( "arial" );
        headerFont.setColor ( IndexedColors.BLACK.getIndex() );

        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 8 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        textFont.setFontName( "arial" );
        textFont.setColor ( IndexedColors.BLACK.getIndex() );

        CellStyle titleStyle  = workbook.createCellStyle ( );
        titleStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        titleStyle.setBorderTop ( CellStyle.BORDER_THIN );
        titleStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        titleStyle.setBorderRight ( CellStyle.BORDER_THIN );
        titleStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        titleStyle.setWrapText ( true );
        titleStyle.setFont ( titleFont );

        CellStyle headerStyle  = workbook.createCellStyle ( );
        headerStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        headerStyle.setBorderTop ( CellStyle.BORDER_THIN );
        headerStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        headerStyle.setBorderRight ( CellStyle.BORDER_THIN );
        headerStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        headerStyle.setWrapText ( true );
        headerStyle.setFont ( headerFont );

        CellStyle textStyle  = workbook.createCellStyle ( );
        textStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        textStyle.setBorderTop ( CellStyle.BORDER_THIN );
        textStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        textStyle.setBorderRight ( CellStyle.BORDER_THIN );
        textStyle.setAlignment ( CellStyle.ALIGN_CENTER );
        textStyle.setWrapText ( true );
        textStyle.setFont ( textFont );

        CellStyle valueStyle  = workbook.createCellStyle ( );
        valueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        valueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        valueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        valueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        valueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        valueStyle.setWrapText ( true );
        valueStyle.setFont ( textFont );
        valueStyle.setDataFormat( df.getFormat( "0" ) );


        CellStyle valueFloatStyle  = workbook.createCellStyle ( );
        valueFloatStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        valueFloatStyle.setBorderTop ( CellStyle.BORDER_THIN );
        valueFloatStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        valueFloatStyle.setBorderRight ( CellStyle.BORDER_THIN );
        valueFloatStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        valueFloatStyle.setWrapText ( true );
        valueFloatStyle.setFont ( textFont );
        valueFloatStyle.setDataFormat( workbook.createDataFormat().getFormat( numberFormat ) );

        CellStyle numericvalueStyle  = workbook.createCellStyle ( );
        numericvalueStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        numericvalueStyle.setBorderTop ( CellStyle.BORDER_THIN );
        numericvalueStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        numericvalueStyle.setBorderRight ( CellStyle.BORDER_THIN );
        numericvalueStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        numericvalueStyle.setWrapText ( true );
        numericvalueStyle.setFont ( textFont );
        numericvalueStyle.setDataFormat( workbook.createDataFormat().getFormat( "$ #,##0.0000;[Red]$ (#,##0.0000)" ) );

        Row row;

        ArrayList columnNames = (ArrayList)lotInfoList.get ( 0 );

        Cell cell = mergeCells ( sheet, 0, 0, 0, (columnNames.size()-1), titleStyle );
        setCellValue ( cell,  ch.createRichTextString(reportHeading), titleStyle );

        //SETTING HEADINGS
        row = sheet.createRow ( 2 );
        for ( int i = 0; i < columnNames.size(); i++ )
        {
            cell    = row.createCell( i );
            setCellValue ( cell, ch.createRichTextString((String)columnNames.get(i)), headerStyle );
            int width = ( ((String)columnNames.get(i)).length() > 8 ) ? ((String)columnNames.get(i)).length() : 8;
            sheet.setColumnWidth( i, (short) ( ( ( width + 4 ) * 256 ) ) );
        }

        int nextRowNumber   = 3;
        int nextColNumber;

        for ( int i = 1; i < lotInfoListSize; i++ )
        {
            nextColNumber   = 0;
            ArrayList colList =  ( ArrayList )lotInfoList.get ( i );
            row = sheet.createRow( nextRowNumber++ );
            for (int j = 0; j < colList.size(); j++) {
                DBColumn dbColumn = (DBColumn) colList.get(j);
                Object dbColumnObj = dbColumn.getColValObj();
                try
                {
                    if ( dbColumn.getColType() == java.sql.Types.INTEGER )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new Integer( 0 );
                        }
                        setCellValue( row, nextColNumber++, ((Integer)dbColumnObj).intValue(), valueStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.BIGINT )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new Long( 0 );
                        }
                        setCellValue( row, nextColNumber++, ((Long)dbColumnObj).longValue(), valueStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.FLOAT )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new Double( 0.0f );
                        }
                        setCellValue( row, nextColNumber++, ((Double)dbColumnObj).doubleValue(), valueFloatStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.DOUBLE )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new Double( 0.0f );
                        }
                        setCellValue( row, nextColNumber++, ((Double)dbColumnObj).doubleValue(), valueFloatStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.NUMERIC )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  new BigDecimal( 0.0f );
                        }
                        setCellValue( row, nextColNumber++, ((BigDecimal)dbColumnObj).doubleValue(), numericvalueStyle, Cell.CELL_TYPE_NUMERIC );
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.VARCHAR )
                    {
                        if ( dbColumnObj == null )
                        {
                            dbColumnObj =  "";
                        }
                        setCellValue( row, nextColNumber++, ch.createRichTextString(dbColumnObj.toString()), textStyle );
                        if ( ( ( dbColumnObj.toString().length() + 4 ) * 256 ) > sheet.getColumnWidth( j ) )
                        {
                            sheet.setColumnWidth( j, (short) ( ( ( dbColumnObj.toString().length() + 4 ) * 256 ) ) );
                        }
                    }
                    else if ( dbColumn.getColType() == java.sql.Types.TIMESTAMP )
                    {
                        setCellValue( row, nextColNumber++, ch.createRichTextString(StringUtils.formatDateToString ( (Date)dbColumnObj , null ) ), textStyle );
                    }
                    else
                    {
                        setCellValue( row, nextColNumber++, ch.createRichTextString(""), textStyle );
                    }

                }
                catch( Exception e)
                {
                    setCellValue( row, nextColNumber++, ch.createRichTextString(dbColumnObj.toString()), textStyle );
                    e.printStackTrace();
                }
            }
        }
        return workbook;
    }

    /**
     * merges cells and sets each cell with the given cellStyle.
     * @param sheet
     * sheet object in which the cells has to be merged.
     * @param startRow
     * start row Number to merge from.<br>
     * starts from 0.
     * @param startCell
     * start cell number in the start row to merge from.<br>
     * starts from 0.
     * @param endRow
     * end row Number<br>
     * starts from 0.
     * @param endCell
     * end cell number<br>
     * starts from 0.
     * @param cellStyle
     * end cell number<br>
     * starts from 0.
     * @return
     * returns cell object of the startRow, startCell.
     */
    private static Cell mergeCells ( Sheet sheet, int startRow, int startCell, int endRow, int endCell, CellStyle cellStyle )
    {
        //sheet.addMergedRegion( new Region( startRow, startCell, endRow, endCell ) );
        sheet.addMergedRegion( new CellRangeAddress( startRow, endRow, startCell, endCell ) );
        Cell cell;
        for ( int rowIdx = startRow; rowIdx <= endRow; rowIdx++ )
        {
            if ( sheet.getRow( rowIdx ) == null )
            {
                sheet.createRow( rowIdx );
            }
            for ( int cellIdx = startCell; cellIdx <= endCell; cellIdx++ )
            {
                cell = getCellAt ( sheet.getRow ( rowIdx ), cellIdx );
                cell.setCellStyle ( cellStyle );
            }
        }
        return getCellAt ( sheet.getRow( startRow), startCell );
    }
    /**
     * Returns the cell object at the specified cell in the row.<br>
     * It creats cell if doesn't exists.
     * @param row
     * row object from which cell object has to be picked
     * @param cellNumber
     * starts from 0.
     * @return returns cell
     */
    private static Cell getCellAt ( Row row, int cellNumber )
    {
        Cell cell;
        cell = row.getCell( cellNumber );
        if ( cell == null )
        {
            cell = row.createCell ( cellNumber );
        }
        return cell;
    }
    private static void setCellValue ( Cell cell, RichTextString value, CellStyle cellStyle )
    {
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
    }

    private static void setCellValue ( Row row, int cellNo, RichTextString value, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( Cell.CELL_TYPE_STRING );
    }

    private static void setCellValue ( Row row, int cellNo, int value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }

    private static void setCellValue ( Row row, int cellNo, double value, CellStyle cellStyle, int cellType )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellValue( value );
        cell.setCellType( cellType );
    }

	public static Workbook formatWipBacklog(Workbook workbook)
	{
		Sheet sheet                 = null;
		sheet = workbook.getSheet("Wip Vs BackLog Planning Report");
        Row row = null;
        Cell cell = null;
        String customerName = null;
        
        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 8 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_BOLD );
        textFont.setFontName( "arial" );
        textFont.setColor ( IndexedColors.BLACK.getIndex() );

        CellStyle valueStyleColor  = workbook.createCellStyle ( );
        valueStyleColor.setBorderBottom ( CellStyle.BORDER_THIN );
        valueStyleColor.setBorderTop ( CellStyle.BORDER_THIN );
        valueStyleColor.setBorderLeft ( CellStyle.BORDER_THIN );
        valueStyleColor.setBorderRight ( CellStyle.BORDER_THIN );
        valueStyleColor.setAlignment ( CellStyle.ALIGN_CENTER );
        valueStyleColor.setFillPattern(CellStyle.SOLID_FOREGROUND );
        valueStyleColor.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.getIndex() );
        valueStyleColor.setWrapText ( true );
        valueStyleColor.setFont ( textFont );
        valueStyleColor.setDataFormat( workbook.createDataFormat().getFormat( "###0;[Red](###0)" ) );   //BuiltinFormat "(#,##0_);[Red](#,##0)" )

        for (int rowCount = 3; rowCount < sheet.getPhysicalNumberOfRows(); rowCount++)
        {
        	row = sheet.getRow(rowCount);
        	if( row != null )
        	{
        		cell = row.getCell( (int)row.getFirstCellNum() );
        		customerName = cell.getRichStringCellValue().getString();
        		if( customerName == null || customerName.trim().length() == 0 )
        		{
        			for( int cellCount = 0; cellCount < row.getPhysicalNumberOfCells(); cellCount++ )
        			{
        				cell = row.getCell( cellCount);
        				cell.setCellStyle(valueStyleColor);
        			}
        		}
        	}
        }
        
        return workbook;
	}

	public static void groupWorkbook(Workbook workbook, int sheetIndex, int rowIdx, int groupCol, ArrayList<String> groupColArray )
	{
        CreationHelper ch = workbook.getCreationHelper();
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row 	= null;
		String basePart = null;
		String newBasePart = null;
        int groupStartIdx = 0;
        
        Font textFont = workbook.createFont();
        textFont.setFontHeightInPoints ( ( short ) 8 );
        textFont.setBoldweight ( Font.BOLDWEIGHT_NORMAL );
        textFont.setFontName( "arial" );
        textFont.setColor ( IndexedColors.BLACK.getIndex() );
        
        CellStyle textStyle  = workbook.createCellStyle ( );
        textStyle.setBorderBottom ( CellStyle.BORDER_THIN );
        textStyle.setBorderTop ( CellStyle.BORDER_THIN );
        textStyle.setBorderLeft ( CellStyle.BORDER_THIN );
        textStyle.setBorderRight ( CellStyle.BORDER_THIN );
        textStyle.setAlignment ( CellStyle.ALIGN_RIGHT );
        textStyle.setFillPattern(CellStyle.SOLID_FOREGROUND );
        textStyle.setFillForegroundColor( IndexedColors.GREY_25_PERCENT.getIndex() );
        textStyle.setWrapText ( true );
        textStyle.setFont ( textFont );
        
        for( int i = rowIdx; i <= sheet.getPhysicalNumberOfRows(); i++ )
        {
        	row = sheet.getRow( i );
        	if( basePart == null )
        	{
        		basePart = row.getCell(groupCol).getRichStringCellValue().getString();
        		//sheet.shiftRows(i, sheet.getPhysicalNumberOfRows(), 1, true, true );
        		//sheet.createRow(i);
                //setCellValue ( sheet.getRow(i),(short) 0, basePart, textStyle );
        		groupStartIdx = i;
        	}
        	else
        	{
        		if( !basePart.equalsIgnoreCase( row.getCell(groupCol).getRichStringCellValue().getString() ) )
        		{
        			newBasePart = row.getCell(groupCol).getRichStringCellValue().getString();
        			sheet.shiftRows(i, sheet.getPhysicalNumberOfRows(), 1, true, true );
        			sheet.createRow(i);
        			setCellValue ( sheet.getRow(i), 0, ch.createRichTextString(basePart), textStyle );
        			basePart = newBasePart;
        			i = i+1;
        		}
        	}
         }
		sheet.createRow(sheet.getPhysicalNumberOfRows()+1);
		setCellValue ( sheet.getRow(sheet.getPhysicalNumberOfRows()), 0, ch.createRichTextString(basePart), textStyle );
        
        //sheet.groupRow( rowIdx, sheet.getPhysicalNumberOfRows() );
        basePart = null;
        
        for( int i = rowIdx; i < sheet.getPhysicalNumberOfRows(); i++ )
        {
           	row = sheet.getRow(i);
        	if( basePart == null )
        	{
        		basePart = row.getCell(groupCol).getRichStringCellValue().getString();
        		groupStartIdx = i;
        	}
        	else
        	{
        		if( !basePart.equalsIgnoreCase( row.getCell(groupCol).getRichStringCellValue().getString() ) )
        		{
        			//System.out.println("base Part "+ basePart + "		" + row.getCell(0).getStringCellValue() );
        			//System.out.println("	groupStartIdx	" + ( groupStartIdx ) + "	i	" + (i-2));
       				sheet.groupRow( groupStartIdx, i-2 );
       				
       				for( int x = 0; x < row.getPhysicalNumberOfCells(); x++  )
       				{
       					if( groupColArray.contains(x+"") )
       					{
       						setSumFormula(sheet, groupStartIdx, i-1, x );
       					}       					
       				}
       				
        			sheet.setRowGroupCollapsed( groupStartIdx, true);
        			basePart = row.getCell(groupCol).getRichStringCellValue().getString();
        			groupStartIdx = i;
        		}
        	}
        }
        sheet.groupRow( groupStartIdx, sheet.getPhysicalNumberOfRows()-1 );
        sheet.setRowGroupCollapsed( groupStartIdx, true);
		for( int x = 0; x < row.getPhysicalNumberOfCells(); x++  )
		{
			if( groupColArray.contains(x+"") )
			{
				setSumFormula(sheet, groupStartIdx, sheet.getPhysicalNumberOfRows(), x );
			}       					
		}
	}
	
    private static String getAlphaforIdx ( int idx )
    {
        String[] alpha = {"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"};
        String formatted = "";
        if ( idx < alpha.length )
        {
            formatted = alpha[idx];
        }
        else
        {
            //idx=idx+1;
            int idx1 = (idx/26)-1;
            int idx2 = (idx%26);
            if ( idx1 >= 0 )
            {
                formatted = alpha[idx1];
            }
            if ( idx2 >= 0 )
            {
                formatted = formatted+alpha[idx2];
            }
        }
        return formatted;
    }
    
    /**
    *
    * @param columnIdx  zero based
    * @param rowNumber starts from one
    * @return
    */
    private static String getCellReference ( int columnIdx, int rowNumber )
    {
    	return getAlphaforIdx( columnIdx ) + rowNumber;
    }
    
    private static void setCellFormula ( Row row, int cellNo, String formula, CellStyle cellStyle )
    {
        Cell cell   = row.createCell( cellNo );
        if ( cellStyle != null )
        {
            cell.setCellStyle( cellStyle);
        }
        cell.setCellFormula( formula );
    }

	private static void setSumFormula( Sheet sheet, int rowStartNumber, int rowEndNumber, int cellNumber )
	{
		Row row = sheet.getRow( rowEndNumber );
        String totalFormula = "SUM(" + getCellReference(cellNumber, rowStartNumber+1) + ":" + getCellReference(cellNumber, (rowEndNumber )) + ")";
        setCellFormula( row, cellNumber, totalFormula, row.getCell(0).getCellStyle() );
	}

    /** utility method to create excel workbook for the given dataList. Column Properties will be applied to the specified
     * columns, if not specidifed a properties for the column then the defaults will be applied.
     * <br>The follwoing are the default properties will be applied for the workbook:
     * <br> <b>Data Column Type</b>&nbsp;&nbsp;=&nbsp;&nbsp;&nbsp;<b>Cell Style(see {@link ExcelUtils} class for the details)</b>
     * <br>java.sql.Types.INTEGER =  excelUtils.CELL_STYLE_NUMBER
     * <br>java.sql.Types.BIGINT =  excelUtils.CELL_STYLE_NUMBER
     * <br>java.sql.Types.FLOAT =  excelUtils.CELL_STYLE_FLOAT
     * <br>java.sql.Types.DOUBLE =  excelUtils.CELL_STYLE_FLOAT
     * <br>java.sql.Types.NUMERIC =  excelUtils.CELL_STYLE_NUMERIC
     * <br>java.sql.Types.VARCHAR =  excelUtils.CELL_STYLE_TEXT
     * <br>java.sql.Types.TIMESTAMP =  excelUtils.CELL_STYLE_DATE
     * <br>java.sql.Types.DATE =  excelUtils.CELL_STYLE_DATE
     * <br>
     * <br>To apply a user defined style to a column in the work pass the list of ExcelColumnProperties.
     * <br>Eg: To apply the Currency style to the column say 'Total Revenue' then create the ExcelColumnProperties as follows:
     * <br><code> ExcelColumnProperties colProps = new ExcelColumnProperties("Total Revenue"); //or you can pass the column index(0-based).
     * <br> colPros.setCellStyle(ExcelColumnProperties.CELL_STYLE_CURRENCY_$)</cde>
     * <br> set column properties whichever you need and pass it.
     * @param dataList
     * @param reportHeading
     * @param fromDate
     * @param toDate
     * @param columnPropertiesList list of ExcelColumnProperties objects to be applied.
     * @return
     * @throws ScalarException
     */
    public static Workbook getWorkbook(ArrayList dataList, String reportHeading, String fromDate, String toDate, List<ExcelColumnProperties> columnPropertiesList, Workbook wb) throws ScalarException, IOException {

        int dataListSize = (dataList == null) ? 0 : dataList.size();
        Workbook workbook;
        Sheet sheet;


        if ( "xlsx".equalsIgnoreCase(ConfigManager.getXLSWorkBookFormat() ) )
        {
            workbook =  (wb == null ?  new XSSFWorkbook():  wb);
        }
        else
        {
            workbook =  (wb == null ?  new HSSFWorkbook():  wb);
        }
        workbook.setFirstVisibleTab(0);
        CreationHelper ch = workbook.getCreationHelper();
        DataFormat df     = ch.createDataFormat();
        sheet = workbook.createSheet(reportHeading != null && reportHeading.length() > 31 ? reportHeading.substring(0, 29) + ".." : reportHeading);
        //sheet.setSelected(true);

        ExcelUtils excelUtils = new ExcelUtils(workbook);

        CellStyle titleStyle = excelUtils.CELL_STYLE_TITLE;
        CellStyle headerStyle = excelUtils.CELL_STYLE_HEADER;
        CellStyle textStyle = excelUtils.CELL_STYLE_TEXT;
        CellStyle valueStyle = excelUtils.CELL_STYLE_NUMBER;
        CellStyle dateValueStyle = excelUtils.CELL_STYLE_DATE;
        CellStyle valueFloatStyle = excelUtils.CELL_STYLE_FLOAT;
        CellStyle numericvalueStyle = excelUtils.CELL_STYLE_NUMERIC;

        int nextRowNumber   = 0;
        int endRow = 0;
        ArrayList columnNames = (ArrayList) dataList.get(0);
        Row row = sheet.createRow( nextRowNumber++ );
        Cell cell = excelUtils.mergeCells(sheet, 0, 0, 0, (columnNames.size() - 1), titleStyle);
        excelUtils.setCellValue(cell, ch.createRichTextString(reportHeading), titleStyle);

        int nextColumnIdx = 0;
        if ( fromDate != null && fromDate.trim().length() > 7 )
        {
            row = sheet.createRow ( nextRowNumber++ ); //empty row

            excelUtils.setCellValue ( row, nextColumnIdx++, ch.createRichTextString("From Date :"), headerStyle );
            excelUtils.setCellValue( row, nextColumnIdx++, ch.createRichTextString(fromDate), textStyle  );
            excelUtils.setCellValue( row, nextColumnIdx++, ch.createRichTextString(""), textStyle  );
            /*if (toDate == null || toDate.trim().length() < 7 )
            {
                toDate = StringUtils.formatDateToString(new Date() );
            }*/
        }
        if ( (fromDate == null || fromDate.trim().length() == 0 ) && toDate != null && toDate.trim().length() > 7 )
        {
            row = sheet.createRow ( nextRowNumber++ ); //empty row

            excelUtils.setCellValue( row, nextColumnIdx++, ch.createRichTextString(""), textStyle  );
            excelUtils.setCellValue( row, nextColumnIdx++, ch.createRichTextString(""), textStyle  );
            excelUtils.setCellValue( row, nextColumnIdx++, ch.createRichTextString(""), textStyle  );
            excelUtils.setCellValue (row, nextColumnIdx++, ch.createRichTextString("To Date :"), headerStyle );
            excelUtils.setCellValue( row, nextColumnIdx++, ch.createRichTextString(toDate), textStyle  );
        }
        else if ( fromDate != null && fromDate.trim().length() > 7 && toDate != null && toDate.trim().length() > 7 )
        {
            excelUtils.setCellValue ( row, nextColumnIdx++, ch.createRichTextString("To Date :"), headerStyle );
            excelUtils.setCellValue( row, nextColumnIdx++, ch.createRichTextString(toDate), textStyle  );
        }

        row = sheet.createRow ( nextRowNumber++ ); //empty row

        //SETTING HEADINGS
        row = sheet.createRow(nextRowNumber++);
        for (int i = 0; i < columnNames.size(); i++) {
            cell = row.createCell(i);
            excelUtils.setCellValue(cell, ch.createRichTextString((String) columnNames.get(i) ), headerStyle);
            int width = (((String) columnNames.get(i)).length() > 8) ? ((String) columnNames.get(i)).length() : 8;
            if ( columnPropertiesList != null && columnPropertiesList.size() > 0 )
            {
                ExcelColumnProperties colProps = getColumnProperties((String) columnNames.get(i), i, columnPropertiesList);
                if (colProps != null && colProps.getColWidth() > 0) {
                    sheet.setColumnWidth(i, colProps.getColWidth());
                } else {
                    sheet.setColumnWidth(i, ((width + 4) * 256));
                }
            }
            else
            {
               sheet.setColumnWidth(i, ((width + 4) * 256));
            }
        }

        int startRowIdx = nextRowNumber;
        int nextColNumber;
        SubRow subRow = new SubRow(startRowIdx);
        for (int i = 1; i < dataListSize; i++) {
            nextColNumber = 0;
            ArrayList colList = (ArrayList) dataList.get(i);
            row = sheet.createRow(nextRowNumber++);
            
            for (int j = 0; j < colList.size(); j++) {
                DBColumn dbColumn = (DBColumn) colList.get(j);
                Object dbColumnObj = dbColumn.getColValObj();
                CellStyle cellStyle = null;
				ExcelColumnProperties colProps = null;
                if ( columnPropertiesList != null && columnPropertiesList.size() > 0 )
                {
                    colProps = getColumnProperties((String) columnNames.get(j), j, columnPropertiesList);
                    cellStyle = colProps==null ? null : colProps.getCellStyle(excelUtils);
                }
                else
                {
                    cellStyle = null;
                }
                try {
                    if (dbColumn.getColType() == java.sql.Types.INTEGER) {
                        if (dbColumnObj == null) {
                            dbColumnObj = 0;
                        }

                        excelUtils.setCellValue(row, nextColNumber++, ((Integer) dbColumnObj).intValue(), cellStyle != null ? cellStyle : valueStyle, Cell.CELL_TYPE_NUMERIC);
                    } else if (dbColumn.getColType() == java.sql.Types.BIGINT) {
                        if (dbColumnObj == null) {
                            dbColumnObj = (long) 0;
                        }
                        excelUtils.setCellValue(row, nextColNumber++, (Long) dbColumnObj, cellStyle != null ? cellStyle : valueStyle, Cell.CELL_TYPE_NUMERIC);
                    } else if (dbColumn.getColType() == java.sql.Types.FLOAT) {
                        if (dbColumnObj == null) {
                            dbColumnObj = (double) 0.0f;
                        }
                        excelUtils.setCellValue(row, nextColNumber++, (Double) dbColumnObj, cellStyle != null ? cellStyle : valueFloatStyle, Cell.CELL_TYPE_NUMERIC);
                    } else if (dbColumn.getColType() == java.sql.Types.DOUBLE) {
                        if (dbColumnObj == null) {
                            dbColumnObj = (double) 0.0f;
                        }
                        excelUtils.setCellValue(row, nextColNumber++, (Double) dbColumnObj, cellStyle != null ? cellStyle : valueFloatStyle, Cell.CELL_TYPE_NUMERIC);
                    } else if (dbColumn.getColType() == java.sql.Types.NUMERIC) {
                        if (dbColumnObj == null) {
                            dbColumnObj = new BigDecimal(0.0f);
                        }
                        excelUtils.setCellValue(row, nextColNumber++, ((BigDecimal) dbColumnObj).doubleValue(), cellStyle != null ? cellStyle : numericvalueStyle, Cell.CELL_TYPE_NUMERIC);
                    } else if (dbColumn.getColType() == java.sql.Types.VARCHAR) {
                        if (dbColumnObj == null) {
                            dbColumnObj = "";
                        }
						if(colProps != null && colProps.isFormula()) {
							String formula = dbColumnObj.toString();
							Pattern pattern = Pattern.compile("\\$\\{[^\\{\\}]*\\}", Pattern.CASE_INSENSITIVE);
							Matcher m = pattern.matcher(formula);
        					while (m.find()) {
								String expr = m.group();
								String colName = expr.substring(2, expr.length()-1);
								int colIdx = columnNames.indexOf(colName);
								int rowIdx = row.getRowNum();
								formula = formula.replace(expr, excelUtils.getCellReference(colIdx, rowIdx));
							}
							excelUtils.setCellFormula(row, nextColNumber++, formula, cellStyle != null ? cellStyle : textStyle);
						} else {
							excelUtils.setCellValue(row, nextColNumber++, ch.createRichTextString(dbColumnObj.toString()), cellStyle != null ? cellStyle : textStyle);
						}
                    } else if (dbColumn.getColType() == java.sql.Types.TIMESTAMP) {
                        if (dbColumnObj == null) {
                            excelUtils.setCellValue(row, nextColNumber++, ch.createRichTextString(""), textStyle);
                        } else {
                            excelUtils.setCellValue(row, nextColNumber++, ch.createRichTextString(StringUtils.formatDateToString((Date) dbColumnObj, null) ), cellStyle != null ? cellStyle : dateValueStyle);
                        }
                    } else if (dbColumn.getColType() == java.sql.Types.DATE) {
                        if (dbColumnObj == null) {
                            excelUtils.setCellValue(row, nextColNumber++, ch.createRichTextString(""), textStyle);
                        } else {
                            excelUtils.setCellValue(row, nextColNumber++, ch.createRichTextString(StringUtils.formatDateToString((Date) dbColumnObj, null)), cellStyle != null ? cellStyle : dateValueStyle);
                        }
                    } else {
                        excelUtils.setCellValue(row, nextColNumber++, ch.createRichTextString(""), textStyle);
                    }

                    if ( colProps != null && colProps.isGroupBy())
                    {
                        ArrayList nextColList = null;
                        Object nextdbColumnObj = null;

                        if ( (i+1) < dataListSize )
                        {
                            nextColList = (ArrayList) dataList.get(i+1);
                            DBColumn nextdbColumn = (DBColumn) nextColList.get(j);
                            nextdbColumnObj = nextdbColumn.getColValObj();
                        }
                        
                        if ( nextdbColumnObj == null || !dbColumnObj.toString().equalsIgnoreCase( nextdbColumnObj.toString() ) )
                        {
                            Row row1 = null;
                            row1 = sheet.createRow(nextRowNumber++);
                            subRow.add(j, row1.getRowNum());
                            int nextColNumber1=0;
                            for (int colIdx = 0; colIdx < colList.size(); colIdx++)
                            {
                                CellStyle subtotalStyle = null;
                                ExcelColumnProperties subTotalcolProps = null;
                                if ( columnPropertiesList != null && columnPropertiesList.size() > 0 ) {
                                    subTotalcolProps = getColumnProperties((String) columnNames.get(colIdx), colIdx, columnPropertiesList);
                                    subtotalStyle = subTotalcolProps==null ? null : subTotalcolProps.getTotalcellStyle(excelUtils);
                                }


                                if (subTotalcolProps != null && subTotalcolProps.isGroupBy()) {
                                    String subTotalStr = "";
                                    if(j == colIdx) {
                                        subTotalStr = dbColumnObj.toString().toUpperCase()+" SUB TOTAL:";
                                    }
                                    excelUtils.setCellValue(row1, nextColNumber1++, ch.createRichTextString(subTotalStr), headerStyle);
                                } else if ( subTotalcolProps != null && subTotalcolProps.isShowSubTotal() ) {
                                    //excelUtils.setCellValue(row1, nextColNumber1++, ch.createRichTextString("VALAUU"), textStyle);
                                    //excelUtils.setSumFormula( sheet, row1, startRowIdx, (row1.getRowNum()-1), nextColNumber1++, null, subtotalMap );
                                    int subStartRowIdx = subRow.getColPreviousSubRowIdx(j, row1.getRowNum());
                                    int subEndRowIdx = row1.getRowNum();
                                    StringBuilder formula = new StringBuilder();
                                    formula.append("SUM(").append(excelUtils.getCellReference(colIdx, subStartRowIdx+1)).append(":").append(excelUtils.getCellReference(colIdx, subEndRowIdx-1)).append(")");
                                    String subRows = subRow.getSubRowsInRange(subStartRowIdx, subEndRowIdx);
                                    if(subRows != null && subRows.length() >0) {
                                        String[] subRowIdxs = subRows.split(",");
                                        for(int subRowIdx=0; subRowIdx < subRowIdxs.length; subRowIdx++) {
                                            formula.append("-").append(excelUtils.getCellReference(colIdx, Integer.parseInt(subRowIdxs[subRowIdx])));
                                        }
                                    }
                                    excelUtils.setCellFormula(row1, nextColNumber1++, formula.toString(), subtotalStyle);
                                }
                                else
                                {
                                    excelUtils.setCellValue(row1, nextColNumber1++, ch.createRichTextString(""), headerStyle);
                                }
                            }
                        }
                    }
                }
                catch (Exception e) {
                    excelUtils.setCellValue(row, nextColNumber++, ch.createRichTextString(dbColumnObj.toString()), textStyle);
                    e.printStackTrace();
                }
            }
        }

        //for totals
        int endRowIdx = nextRowNumber - 1;

        if (startRowIdx <= endRowIdx) { //data is available so we show the totals.
            Row totalsRow = null;
            for (int i = 0; i < columnNames.size(); i++) {
                ExcelColumnProperties colProps = getColumnProperties((String) columnNames.get(i), i, columnPropertiesList);
                if (colProps != null && colProps.isShowTotal()) {
                    if (totalsRow == null) {
                        totalsRow = sheet.createRow(nextRowNumber + 1);
                    }
                    int subStartRowIdx = startRowIdx;
                    int subEndRowIdx = endRowIdx;
                    StringBuilder formula = new StringBuilder();
                    formula.append("SUM(").append(excelUtils.getCellReference(i, subStartRowIdx)).append(":").append(excelUtils.getCellReference(i, subEndRowIdx)).append(")");
                    String subRows = subRow.getSubRowsInRange(subStartRowIdx, subEndRowIdx+1);
                    if(subRows != null && subRows.length() >0) {
                        String[] subRowIdxs = subRows.split(",");
                        for(int subRowIdx=0; subRowIdx < subRowIdxs.length; subRowIdx++) {
                            formula.append("-").append(excelUtils.getCellReference(i, Integer.parseInt(subRowIdxs[subRowIdx])));
                        }
                    }
                    //excelUtils.setSumFormula(sheet, totalsRow, startRowIdx, endRowIdx, i, null);
                    excelUtils.setCellValue(totalsRow, 0, ch.createRichTextString("GRAND TOTAL : "), headerStyle);
                    excelUtils.setCellFormula(totalsRow, i, formula.toString(), colProps.getTotalcellStyle(excelUtils));
                }
            }
        }

        return workbook;
    }

    public static Workbook getWorkbook(ArrayList dataList, String reportHeading, String fromDate, String toDate, List<ExcelColumnProperties> columnPropertiesList) throws ScalarException, IOException
    {
        return getWorkbook(dataList, reportHeading, fromDate, toDate, columnPropertiesList, null);
    }
    private static ExcelColumnProperties getColumnProperties(String columnName, int colIdx, List<ExcelColumnProperties> columnPropertiesList) {
        if (columnPropertiesList != null) {
            for (ExcelColumnProperties colProps : columnPropertiesList) {
                if (colProps != null) {
                    if(colProps.getColName() != null && colProps.getColName().equalsIgnoreCase(columnName) ) {
                        return colProps;
                    } else if( colProps.getColIdx() == colIdx ) {
                        return colProps;
                    }
                }
            }
        }
        return null;
    }
}
class SubRow {
    ArrayList<String> subRowList = new ArrayList<String>();
    final int startRowIdx;
    public SubRow(int startRowIdx) {
        this.startRowIdx = startRowIdx;
    }
    public void add(int colidx, int rowidx) {
        subRowList.add(colidx +":"+rowidx);
    }
    public int getColPreviousSubRowIdx(int colidx, int rowidx) {
        if(!subRowList.isEmpty()) {
            int size = subRowList.size();
            for (int i= size-1; i>=0; i-- )
            {
              String[] colrow = subRowList.get(i).split(":");
                int colidx1 = Integer.parseInt(colrow[0]);
                int rowidx1 = Integer.parseInt(colrow[1]);
                if(rowidx != rowidx1 && colidx == colidx1) {
                    return rowidx1;
                }
             }
        }
        return startRowIdx-1;
    }

    /**
     *
     * @param startRowIdx
     * @param endRowIdx
     * @return returns comma separated row indexes.
     */
    public String getSubRowsInRange(int startRowIdx, int endRowIdx) {
        StringBuilder subrows = new StringBuilder();
        if(!subRowList.isEmpty()) {
            int size = subRowList.size();
            for (int i= size-1; i>=0; i-- )
            {
              String[] colrow = subRowList.get(i).split(":");
                int colidx1 = Integer.parseInt(colrow[0]);
                int rowidx1 = Integer.parseInt(colrow[1]);
                if(rowidx1 > startRowIdx && rowidx1 < endRowIdx) {
                    if(subrows.length()>0) {
                        subrows.append(",");
                    }
                    subrows.append(rowidx1);
                }
            }
        }
        return subrows.toString();
    }
}